<?php
class Message_model extends CI_Model {
	
	function __construct()
    {
        // Call the Model constructor
        parent::__construct();
    }
    
    public function find_by_id ($id) {
    	$this->db->where('id', intval($id));
		return $this->db->get('messages')->row_array();
    }
    
	public function add($data) {
		$this->db->insert("messages", $data);
		$message_id = $this->db->insert_id();
		
		return $message_id;
	}
	/**
	 * 发送数量
	 * @param int $thread_id
	 */
	public function get_send_count($user_id) {
		$sql = " select count(*) as total from messages  where (msg_from_id=" . intval($user_id) . $this->get_user_group_sql($user_id, "") . ")";
		$result = $this->db->query($sql)->row_array();
		return $result['total'];
	}
	/**
	 * 发送消息
	 * @param int $user_id
	 * @param int $offset
	 * @param int $limit
	 */
	public function find_send_messages ($user_id, $offset = 0, $limit = 10) {
		$sql = " select m.*,u.nickname from messages m left join users u on m.msg_from_id=u.id where m.msg_from_id=" . intval($user_id);
		$sql .= " order by created desc ";
		$sql .= " limit " . $offset . ", " . $limit;
		
		return $this->db->query($sql)->result_array();
	}
	
	/**
	 * 接收数量
	 * @param int $thread_id
	 */
	public function get_recv_count($user_id, $is_system = 0) {
		$sql = " select count(*) as total from messages m left join users u on m.msg_to_id=u.id where (m.created>=u.created or u.created is null) and (m.msg_to_id=" . intval($user_id). $this->get_user_group_sql($user_id, "m.") . ")";
		if ($is_system) {
			$sql .= " and m.is_system=1 ";
		}
		$result = $this->db->query($sql)->row_array();
		return $result['total'];
	}
	/**
	 * 接收消息
	 * @param int $user_id
	 * @param int $offset
	 * @param int $limit
	 */
	public function find_recv_messages ($user_id, $offset = 0, $limit = 10, $is_system = 0) {
		$sql = " select m.*,u.nickname from messages m left join users u on m.msg_to_id=u.id where (m.created>=u.created or u.created is null) and (m.msg_to_id=" . intval($user_id). $this->get_user_group_sql($user_id, "m.") . ")";
		if ($is_system) {
			$sql .= " and m.is_system=1 ";
		}
		$sql .= " order by m.created desc ";
		$sql .= " limit " . $offset . ", " . $limit;
		return $this->db->query($sql)->result_array();
	}
	
	/**
	 * 私人消息
	 * @param int $user_id
	 * @param int $offset
	 * @param int $limit
	 */
	public function find_private_messages ($user_id, $offset = 0, $limit = 10) {
		$sql = " select m.*,u.nickname from messages m left join users u on m.msg_to_id=u.id where (m.created>=u.created or u.created is null) and ( m.msg_to_id=" . intval($user_id) . $this->get_user_group_sql($user_id, "m.") . ")";
		
		$sql .= " and m.is_system=0 ";

		$sql .= " order by m.created desc ";
		$sql .= " limit " . $offset . ", " . $limit;
		return $this->db->query($sql)->result_array();
	}
	/**
	 * 消息设为已读
	 * @param int $msg_id
	 * 
	 */
	public function set_as_readed($msg_id) {
		$sql = "update messages set checked=1 where id=" . intval($msg_id);
		return $this->db->query($sql);
	}
	
	/**
	 * 获取未读消息
	 * @param int $user_id
	 * @param int $is_system
	 */
	public function get_unread_count($user_id) {
		$sql = " select count(*) as total from messages m left join users u on m.msg_to_id=u.id where (m.created>=u.created or u.created is null) and (m.msg_to_id=" . intval($user_id). $this->get_user_group_sql($user_id, "m.") . ") and m.checked=0 ";
		$result = $this->db->query($sql)->row_array();
		return $result['total'];
	}
	
	/**
	 * 未读消息
	 * @param int $user_id
	 * @param int $offset
	 * @param int $limit
	 * @param int $is_system 是否系统消息
	 */
	public function find_unread_messages ($user_id, $offset = 0, $limit = 10) {
		$sql = " select m.*,u.nickname from messages m left join users u on m.msg_to_id=u.id where (m.created>=u.created or u.created is null) and m.checked=0 and ( m.msg_to_id=" . intval($user_id). $this->get_user_group_sql($user_id, "m.") . ")";
		$sql .= " order by m.created desc ";
		$sql .= " limit " . $offset . ", " . $limit;
		return $this->db->query($sql)->result_array();
	}
	
	public function find_by_related_id($id) {
		$this->db->where('related_id', intval($id));
		return $this->db->get('messages')->result_array();
	}
	
	public function delete($id) {
		return $this->db->delete('messages', array('id' => $id)); 
	}
	
	public function get_user_group_sql($user_id, $prefix) {
		$sql = " select is_vertification from users 
		 	where id=" . intval($user_id);
		$result = $this->db->query($sql)->row_array();
		$is_v = empty($result['is_vertification']) ? 0 : $result['is_vertification'];
		
		$sql = " select count(*) as total from  vertifications  where status= 1  and created_by =" . $user_id . " and type=2";
		$result = $this->db->query($sql)->row_array();
		$is_expert = $result['total'];
		
		$groups = array("-1");
		if ($is_v) {
			$groups[] = "-2";
		} 
		if ($is_expert) {
			$groups[] = "-3";
		}
		
		if (!$is_v && !$is_expert) {
			$groups[] = "-4";
		}
		$str = "";
		foreach($groups as $group ) {
			$str .= " or " . $prefix . "msg_to_id=" . $group . " "; 
		}
		return $str;
	}
}